/***********************************************************************************************************************************
 *    Name: Extended_Events.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE master;
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'DBAdmin') RAISERROR (N'Schema DBAdmin Not Found', 19, 1) WITH LOG;
GO

IF OBJECT_ID(N'DBAdmin.GetXEvents') IS NOT NULL DROP FUNCTION DBAdmin.GetXEvents;
GO
--================================================================================================================================--
--  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
-- Summary:	Returns the contents of an extended events data file as a rowset. XEL format
CREATE FUNCTION DBAdmin.GetXEvents (@base NVARCHAR(64)) RETURNS @xE TABLE (TimeStamp DATETIME NOT Null, Package NVARCHAR(64) NOT Null, Name NVARCHAR(64) NOT Null, Event_Data XML NOT Null) AS BEGIN
DECLARE
  @path		NVARCHAR(255),
  @mdpath	NVARCHAR(255);
SET @path=  DBAdmin.GetInstancePath(N'ErrorDumpDir') + @base + N'_0_*.xel';
IF DBAdmin.ProductVersion() < 11.0 SET @mdpath= REPLACE(@path, N'_0_*.xel', N'_0_*.xem');	-- optional parameter SQL 2012+

WITH xe_file (event_data) AS (SELECT CAST(event_data AS XML) FROM sys.fn_xe_file_target_read_file(@path, @mdpath, Null, Null))
INSERT INTO @xE SELECT
	TimeStamp	= event_data.value ('(/event/@timestamp) [1]', 'DATETIMEOFFSET'),
	Package		= event_data.value ('(/event/@package)   [1]', 'NVARCHAR(64)'),
	Name		= event_data.value ('(/event/@name)      [1]', 'NVARCHAR(64)'),
	Event_Data
  FROM xe_file;
RETURN END;
GO
IF DBAdmin.ProductVersion() < 10 DROP FUNCTION DBAdmin.GetXEvents;
GO

